package com.javaweb.dao.impl;

import com.javaweb.common.StringConstants;
import com.javaweb.dao.ArticleDao;
import com.javaweb.model.Article;
import com.javaweb.model.Category;
import com.javaweb.model.Tag;
import com.javaweb.service.dto.ArticleConditionDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
import org.springframework.util.ObjectUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/10/9 9:51
 */

@Repository
@Slf4j
public class ArticleDaoImpl implements ArticleDao  {

    @Autowired
    JdbcTemplate jdbcTemplate ;


    @Transactional
    @Override
    public int insert(Article article) {
        // Assert.notNull(article)

        String sql = "INSERT INTO tb_article (user_id, title, content, status, " +
                "description, category_id, create_time, update_time, support, opppose, recommended, is_top)" +
                " VALUES (?,?,?,?,?,?,?,?, ?,?)" ;
        Object [] args = new Object[]{
                article.getUserId(),
                article.getTitle(),
                article.getContent(),
                article.getStatus(),
                article.getDescription(),
                article.getCategory().getId(),
                new java.sql.Date(article.getCreateTime().getTime()),
                new java.sql.Date(article.getUpdateTime().getTime()),
                article.getSupportCount() == null ? 0 : article.getSupportCount(),
                article.getOpposeCount() == null ? 0 : article.getOpposeCount(),
                (article.getRecommended() == null || ! article.getRecommended()) ? 0 : 1,
                (article.getIsTop() == null || ! article.getIsTop()) ? 0 : 1

        }  ;
        Integer ret = jdbcTemplate.update(sql, args);
        if (ret == 0) {
            return StringConstants.DAO_FAIL;
        }
        // insert article and tag into tb_article_tag in batch.
        List<Tag> tags = article.getTags();
        if (tags != null && tags.size() > 0) {
            String articleTagSql = "INSERT INTO tb_article_tag (article_id, tag_id) VALUES (?, ?)" ;
            List<Object[]> params = new ArrayList<>() ;
            for (int i=0; i<tags.size(); i++) {
                params.add(new Object[]{article.getId(), tags.get(i).getId()}) ;
            }
            jdbcTemplate.batchUpdate(articleTagSql, params);
        }
        return StringConstants.DAO_SUCCESS;
    }

    @Override
    public int updateContent(Integer id, String newContent) {
        return 0;
    }

    @Override
    public int updateTitle(Integer id, String newTitle) {
        return 0;
    }

    @Override
    public int deleteById(Integer id) {
        return 0;
    }

    @Override
    public List<Article> selectByUserId(Integer userId) {
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.user_id = ?" ;

        return jdbcTemplate.query(sql, new Object[]{userId},
                rs -> {
            Map<Integer, Article> articleMap = new HashMap<>();
            while (rs.next()) {
                Integer articleId = rs.getInt("id");
                Article article = articleMap.get(articleId);
                if (ObjectUtils.isEmpty(article)) {
                    article = new Article();
                    article.setId(articleId);
                    article.setTitle(rs.getString("title"));
                    article.setContent(rs.getString("content"));
                    article.setCreateTime(rs.getDate("create_time"));
                    article.setUpdateTime(rs.getDate("update_time"));
                    article.setTags(new ArrayList<>());
                    articleMap.put(articleId, article);
                }

                Integer tagId = rs.getInt("tag_id");
                if (tagId != null) {
                    Tag tag = new Tag();
                    tag.setId(tagId);
                    tag.setName(rs.getString("tag_name"));
                    tag.setCreateTime(rs.getDate("tag_create_time"));
                    tag.setUpdateTime(rs.getDate("tag_update_time"));
                    article.getTags().add(tag);
                }
                article.setSupportCount(rs.getInt("sup")) ;
                article.setOpposeCount(rs.getInt("opp")) ;
            }
            return new ArrayList<>(articleMap.values());
        });
    }


    @Override
    public int selectId(Integer userId, String title, Date createTime) {
        return 0;
    }

    @Override
    public List<Article> selectByKeyword(String keyword) {
        String sql = "SELECT * FROM tb_article WHERE title LIKE ? OR content LIKE ?" ;
        return jdbcTemplate.query(sql,new Object[]{"%" + keyword + "%"},
                new RowMapper<Article>() {
                    @Override
                    public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Article article = new Article();
                        Category category = new Category() ;
                        article.setId(rs.getInt("id"));
                        article.setTitle(rs.getString("title"));
                        article.setContent(rs.getString("content"));
                        article.setStatus(rs.getInt("status")) ;
                        article.setDescription(rs.getString("description")) ;
                        category.setId(rs.getInt("category_id")) ;
                        article.setCategory(category) ;
                        article.setCreateTime(rs.getDate("create_time")) ;
                        article.setUpdateTime(rs.getDate("update_time")) ;
                        return article;

                    }
                });
    }

    @Override
    public int updateStatus(Integer id, Integer status) {
        String sql = "UPDATE tb_article SET status = ? WHERE id=?" ;
        return jdbcTemplate.update(sql, new Object[]{status, id});
    }

    @Override
    public List<Article> selectByPage(ArticleConditionDto articleCond, int startPage, int pageSize) {

        Assert.notNull(articleCond, "articleCond不能为空");
        String tagPlaceHolder = null ;

        String sql = "SELECT ta.id AS id, " +
                " ta.user_id AS user_id, " +
                " ta.title AS title, " +
                " ta.content AS content, " +
                " ta.status AS status," +
                " ta.create_time AS create_time, " +
                " ta.update_time AS update_time," +
                " ta.description AS description, " +
                // for support count and oppCount
                " (ta.support + com.supCount) AS supCount, " +
                " (ta.oppose + com.oppCount) AS oppCount," +
                " ta.recommended AS recommended, " +
                " ta.is_top AS is_top, " +
                //for category
                " tc.id AS category_id," +
                " tc.name AS category_name, " +
                " tc.order AS order_num," +
                " tc.parent_id AS parent_id," +
                " tc.create_time AS category_create_time, " +
                " tc.update_time AS category_update_time, " +
                // for tags
                " t.id AS tag_id, " +
                " t.name AS tag_name, " +
                " t.create_time AS tag_create_time, " +
                " t.update_time AS tag_update_time " +
                " FROM tb_article ta " +
                // left join category to get a category
                " LEFT JOIN tb_category tc ON ta.category_id = tc.id " +
                // left join tag to get tag list
                " LEFT JOIN tb_article_tag tat " + " ON ta.id = tat.article_id" +
                " LEFT JOIN tb_tag t ON tat.tag_id = t.id " +
                // left join comment to get support and oppose
                " LEFT JOIN (" +
                        " SELECT article_id, " +
                        " IFNULL(COUNT(support),0) AS supCount, " +
                        " IFNULL(COUNT(oppose), 0) AS oppCount" +
                        "  FROM tb_comment GROUP BY article_id" +
                " ) com ON ta.id = com.article_id " +
                " WHERE 1=1 ";
        List<Object> params = new ArrayList<>() ;

        if (! ObjectUtils.isEmpty(articleCond.getId())) {
            sql += " AND ta.id = ? " ;
            params.add(articleCond.getId()) ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getStatus())) {
            sql += " AND ta.status = ? " ;
            params.add(articleCond.getStatus()) ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getTags())) {
            tagPlaceHolder = String.join(",", Collections.nCopies(articleCond.getTags().split(",").length, "?"));
            sql +=  " AND t.name IN ( " + tagPlaceHolder + " ) " ;
            params.addAll(Arrays.asList(articleCond.getTags().split(",")));
        }
        if (! ObjectUtils.isEmpty(articleCond.getKeyword())) {
            sql += " AND ta.title LIKE ? " ;
            params.add("%" + articleCond.getKeyword() + "%") ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getCategoryName())) {
            sql += " AND tc.name LIKE ? " ;
            params.add("%" + articleCond.getCategoryName() + "%") ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getStartCreateTime())) {
            sql += " AND ta.create_time >= ? " ;
            params.add(articleCond.getStartCreateTime()) ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getEndCreateTime())) {
            sql += " AND ta.create_time <= ? ";
            params.add(articleCond.getEndCreateTime());
        }
        sql += " ORDER BY ta.create_time DESC LIMIT ?, ? " ;
        log.info("SQL: {}", sql) ;
        params.add( startPage * pageSize) ;
        params.add(pageSize) ;

        return jdbcTemplate.query(sql, params.toArray(), new ResultSetExtractor<List<Article>>() {
            @Override
            public List<Article> extractData(ResultSet rs) throws SQLException, DataAccessException {
                Map<Integer, Article> existedArticles = new HashMap<>() ;
                Article article = null ;
                Integer articleId = null ;
                Category category = null ;
                while (rs.next()) {
                    articleId = rs.getInt("id") ;
                    article = existedArticles.get(articleId) ;
                    if (article == null) {
                        article = new Article();
                        existedArticles.put(articleId, article);
                        article.setId(articleId)
                                .setUserId(rs.getInt("user_id"))
                                .setTitle(rs.getString("title"))
                                .setContent(rs.getString("content"))
                                .setStatus(rs.getInt("status"))
                                .setDescription(rs.getString("description"))
                                .setCreateTime(rs.getDate("create_time"))
                                .setUpdateTime(rs.getDate("update_time"))
                                .setSupportCount(rs.getInt("supCount"))
                                .setOpposeCount(rs.getInt("oppCount"))
                                .setRecommended(rs.getBoolean("recommended"))
                                .setIsTop(rs.getBoolean("is_top")) ;
                        category = new Category();
                        category.setId(rs.getInt("category_id"))
                                .setName(rs.getString("category_name"))
                                .setOrder(rs.getInt("order_num"))
                                .setParentId(rs.getInt("parent_id"))
                                .setUpdateTime(rs.getDate("category_update_time"))
                                .setCreateTime(rs.getDate("category_create_time")) ;

                        article.setCategory(category)
                                .setTags(new ArrayList<>());
                    }
                    if (rs.getString("tag_name") != null)  {
                        Tag tag = new Tag();
                        tag.setId(rs.getInt("tag_id"))
                                .setName(rs.getString("tag_name"))
                                .setCreateTime(rs.getDate("tag_create_time"))
                                .setUpdateTime(rs.getDate("tag_update_time"));
                        article.getTags().add(tag);
                    }

                }
                return new ArrayList<>(existedArticles.values());
            }
        });
    }

    @Override
    public int selectCount(ArticleConditionDto articleCond) {

        Assert.notNull(articleCond, "articleCond不能为空");
        String tagPlaceHolder = null ;

        String sql = "SELECT COUNT(*) " +
                " FROM tb_article ta " +
                // left join category to get a category
                " LEFT JOIN tb_category tc ON ta.category_id = tc.id " +
                // left join tag to get tag list
                " LEFT JOIN tb_article_tag tat " + " ON ta.id = tat.article_id" +
                " LEFT JOIN tb_tag t ON tat.tag_id = t.id " +
                " WHERE 1=1 ";
        List<Object> params = new ArrayList<>() ;
        if (! ObjectUtils.isEmpty(articleCond.getId())) {
            sql += " AND ta.id = ? " ;
            params.add(articleCond.getId()) ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getStatus())) {
            sql += " AND ta.status = ? " ;
            params.add(articleCond.getStatus()) ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getTags())) {
            tagPlaceHolder = String.join(",", Collections.nCopies(articleCond.getTags().split(",").length, "?"));
            sql +=  " AND t.name IN ( " + tagPlaceHolder + " ) " ;
            params.addAll(Arrays.asList(articleCond.getTags().split(","))) ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getKeyword())) {
            sql += " AND ta.title LIKE ? " ;
            params.add("%" + articleCond.getKeyword() + "%") ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getCategoryName())) {
            sql += " AND tc.name LIKE ? " ;
            params.add("%" + articleCond.getCategoryName() + "%") ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getStartCreateTime())) {
            sql += " AND ta.create_time >= ? " ;
            params.add(articleCond.getStartCreateTime()) ;
        }
        if (! ObjectUtils.isEmpty(articleCond.getEndCreateTime())) {
            sql += " AND ta.create_time <= ? ";
            params.add(articleCond.getEndCreateTime());
        }
        return jdbcTemplate.queryForObject(sql, params.toArray(),Integer.class) ;
    }
}
